package org.auf.model.jdbc;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;
import java.util.LinkedList;

import org.apache.log4j.Logger;

import org.auf.model.dao.IAlertDAO;
import org.auf.to.Alert;
import org.auf.to.User;

public class AlertDAOJDBC implements IAlertDAO {
	
	static Logger log = Logger.getLogger(AlertDAOJDBC.class.getName());
	
	@Override
	public boolean deleteAlert(Alert alert) {
		
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("DELETE FROM tblalert WHERE id=?");
			st.setString(1, alert.getId());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
	}

	@Override
	public Alert selectAlertById(String id) {
		Alert alert = new Alert();
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblalert WHERE id=?");
			st.setString(1, id);
			ResultSet rs = st.executeQuery();
		
			if (rs.next()){
				alert.setId( rs.getString("id"));
				alert.setTitle(rs.getString("title"));
				alert.setBody(rs.getString("body"));
				alert.setDate(rs.getDate("date"));
				alert.setReaded(rs.getBoolean("readed"));
				alert.setReceiver(UserDAOJDBC.getInstance().selectUserByid(rs.getString("receiver")));
				if (rs.next())alert=null;
			}else alert=null;
		
		}catch(Exception e){alert=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return alert;
	}

	@Override
	public Collection<Alert> selectAlertByUser(User user) {
		Collection<Alert> sol = new LinkedList<Alert>();
		
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("SELECT * FROM tblalert WHERE receiver=?");
			st.setString(1, user.getId());
			ResultSet rs = st.executeQuery();
		
			if (rs.next()){
				Alert alert = new Alert();
				alert.setId( rs.getString("id"));
				alert.setTitle(rs.getString("title"));
				alert.setBody(rs.getString("body"));
				alert.setDate(rs.getDate("date"));
				alert.setReaded(rs.getBoolean("readed"));
				alert.setReceiver(UserDAOJDBC.getInstance().selectUserByid(rs.getString("receiver")));
				sol.add(alert);
			}
		
		}catch(Exception e){sol=null;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return sol;
	}

	@Override
	public boolean updateAlert(Alert alert) {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("UPDATE tblpaired SET readed = ? WHERE id = ?");
			st.setBoolean(1, alert.getReaded());
			st.setString(2, alert.getId());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
	}

	@Override
	public boolean insertAlert(Alert alert) {
		int updates=0;
		ConnectionManager cm = ConnectionManager.getInstance();
		Connection db = cm.checkOut();
		
		try{
			PreparedStatement st = db.prepareStatement("INSERT INTO tblalert (id,title,body,date,readed,receiver) values (?,?,?,?,?,?)");
			st.setString(1, alert.getId());
			st.setString(2, alert.getTitle());
			st.setString(3, alert.getBody());
			st.setDate(4, new java.sql.Date(alert.getDate().getTime()));
			st.setBoolean(5, alert.getReaded());
			st.setString (6 ,alert.getReceiver().getId());
			updates =st.executeUpdate();
		
		}catch(Exception e){updates=-1;log.error("Error al generar la sentencia SQL y ejecutarla: "+e);}

		cm.checkIn(db);
		// TODO Auto-generated method stub
		return updates==1;
	}

}
